#![allow(dead_code)]

use std::sync::Arc;

use base64::Engine as _;
use base64::engine::general_purpose;
use once_cell::sync::OnceCell;
use sea_orm::{ActiveModelTrait, ColumnTrait, ConnectionTrait, DatabaseConnection, DatabaseTransaction, DbBackend, EntityTrait, FromQueryResult, IntoActiveModel, Order, QueryFilter, QueryOrder, QueryResult, QuerySelect, Statement, TransactionTrait, Value};
use sea_orm::sea_query::{Condition, Expr, IntoColumnRef};
use sea_orm::sqlx::types::chrono;
use serde::{Deserialize, Serialize};
use sea_orm::Iterable;

/// 数据库操作结果类型
pub type DbResult<T> = Result<T, sea_orm::DbErr>;

#[derive(Debug, Clone ,PartialEq)]
pub enum ConditionOp {
    Eq,          // 等于（=）
    Ne,          // 不等于（!=）
    Gt,          // 大于（>）
    Lt,          // 小于（<）
    Gte,         // 大于等于（>=）
    Lte,         // 小于等于（<=）
    Like,        // 模糊匹配（LIKE '%value%'）
    LikeLeft,    // 左模糊（LIKE '%value'）
    LikeRight,   // 右模糊（LIKE 'value%'）
    IsNull,      // 为空（IS NULL）
    IsNotNull,   // 不为空（IS NOT NULL）
}

/// 条件组操作符
#[derive(Debug, Clone, PartialEq)]
pub enum ConditionGroupOp {
    And,
    Or,
}

/// 条件表达式
#[derive(Debug, Clone)]
pub enum ConditionExpr<M: EntityTrait> {
    Simple(M::Column, ConditionOp, Value),
    Group(ConditionGroupOp, Vec<ConditionExpr<M>>),
}

/// 查询构建器
#[derive(Debug, Clone)]
pub struct QueryBuilder<M: EntityTrait> {
    entity: M,
    conditions: Vec<ConditionExpr<M>>,
    orders: Vec<(M::Column, Order)>,
    limit: Option<u64>,
    offset: Option<u64>,
}

static GLOBAL_DB_POOL: OnceCell<Arc<DBUtil>> = OnceCell::new();

pub fn init_global_db_pool(db_util: Arc<DBUtil>) {
    GLOBAL_DB_POOL
        .set(db_util)
        .expect("Failed to set global DB pool");
}

pub fn get_global_db_util() -> Arc<DBUtil> {
    GLOBAL_DB_POOL
        .get()
        .expect("Global DB util not initialized")
        .clone()
}

impl<M: EntityTrait> QueryBuilder<M>
where
    M::Column: sea_orm::Iterable{
    pub fn new(entity: M) -> Self {
        Self {
            entity,
            conditions: Vec::new(),
            orders: Vec::new(),
            limit: None,
            offset: None,
        }
    }

    /// 添加简单过滤条件（自动忽略空值）
    pub fn filter(mut self, column: M::Column, op: ConditionOp, value: Value) -> Self {
        self.conditions.push(ConditionExpr::Simple(column, op, value));
        self
    }

    /// 添加条件组（AND/OR）
    pub fn add_group(mut self, op: ConditionGroupOp, exprs: Vec<ConditionExpr<M>>) -> Self {
        self.conditions.push(ConditionExpr::Group(op, exprs));
        self
    }

    /// 快速添加字符串类型的 AND 条件（简化 Eq 操作）
    pub fn eq_str(self, column: M::Column, value: Option<String>) -> Self {
        self.filter(
            column,
            ConditionOp::Eq,
            opt_str_to_value(value),
        )
    }

    /// 快速添加字符串类型的 LIKE 条件（AND 连接）
    pub fn like(self, column: M::Column, value: Option<String>) -> Self {
        self.filter(
            column,
            ConditionOp::Like,
            opt_str_to_value(value),
        )
    }

    /// 快速添加 IsNull 条件
    pub fn is_null(mut self, column: M::Column) -> Self {
        self.conditions.push(ConditionExpr::Simple(column, ConditionOp::IsNull, Value::String(None)));
        self
    }

    /// 快速添加 IsNotNull 条件
    pub fn is_not_null(mut self, column: M::Column) -> Self {
        self.conditions.push(ConditionExpr::Simple(column, ConditionOp::IsNotNull, Value::String(None)));
        self
    }

    /// 添加排序条件
    pub fn order_by(mut self, column: M::Column, order: Order) -> Self {
        self.orders.push((column, order));
        self
    }

    /// 设置查询限制
    pub fn limit(mut self, limit: u64) -> Self {
        self.limit = Some(limit);
        self
    }

    /// 设置查询偏移
    pub fn offset(mut self, offset: u64) -> Self {
        self.offset = Some(offset);
        self
    }

    /// 构建查询语句
    fn build_query(&self) -> sea_orm::Select<M> {
        let mut query = M::find();

        // 构建主条件
        let main_condition = self.build_condition();

        // 应用主条件
        query = query.filter(main_condition);

        // 应用排序
        for (column, order) in &self.orders {
            query = query.order_by(column.clone(), order.clone());
        }

        query
    }

    /// 构建条件表达式
    fn build_condition_expr(&self, expr: &ConditionExpr<M>) -> Condition {
        match expr {
            ConditionExpr::Simple(column, op, value) => {
                // 对于IsNull和IsNotNull，跳过值检查（因为不需要值）
                if *op != ConditionOp::IsNull && *op != ConditionOp::IsNotNull && is_empty_value(value) {
                    return Condition::all(); // 空条件，忽略
                }

                let condition = match op {
                    // 处理IsNull和IsNotNull
                    ConditionOp::IsNull => column.is_null(),
                    ConditionOp::IsNotNull => column.is_not_null(),

                    // 处理like相关操作
                    ConditionOp::Like | ConditionOp::LikeLeft | ConditionOp::LikeRight => {
                        // 确保值是字符串类型
                        let s = match value {
                            Value::String(Some(s)) => s,
                            _ => return Condition::all(), // 非字符串类型，忽略
                        };

                        let like_val = match op {
                            ConditionOp::Like => format!("%{}%", s),
                            ConditionOp::LikeLeft => format!("%{}", s),
                            ConditionOp::LikeRight => format!("{}%", s),
                            _ => unreachable!(),
                        };

                        Expr::col(column.into_column_ref()).like(like_val)
                    }

                    // 处理其他操作（eq/gt等）
                    ConditionOp::Eq => column.eq(value.clone()),
                    ConditionOp::Ne => column.ne(value.clone()),
                    ConditionOp::Gt => column.gt(value.clone()),
                    ConditionOp::Lt => column.lt(value.clone()),
                    ConditionOp::Gte => column.gte(value.clone()),
                    ConditionOp::Lte => column.lte(value.clone()),
                };

                Condition::all().add(condition)
            }
            ConditionExpr::Group(op, exprs) => {
                let mut group_condition = match op {
                    ConditionGroupOp::And => Condition::all(),
                    ConditionGroupOp::Or => Condition::any(),
                };

                for expr in exprs {
                    let sub_condition = self.build_condition_expr(expr);
                    group_condition = group_condition.add(sub_condition);
                }

                group_condition
            }
        }
    }

    /// 执行分页查询
    pub async fn paginate(
        self,
        db: &DatabaseConnection,
        page: u64,
        page_size: u64,
    ) -> DbResult<PaginatedResult<M::Model>> {
        let offset = (page - 1) * page_size;
        let data_query = self.build_query().offset(offset).limit(page_size);

        // 创建计数查询
        let mut count_query = self.build_query();
        count_query = count_query.select_only().column_as(Expr::cust("COUNT(*)"), "count");

        // 获取数据
        let items = data_query.all(db).await?;

        // 获取总数
        let count_result = count_query
            .into_model::<CountResult>()
            .one(db)
            .await?;

        let total = count_result.map(|r| r.count as u64).unwrap_or(0);

        // 计算总页数
        let total_pages = if page_size > 0 {
            (total as f64 / page_size as f64).ceil() as u64
        } else {
            0
        };

        Ok(PaginatedResult {
            items,
            total,
            page,
            page_size,
            total_pages,
        })
    }

    /// 获取单个记录
    pub async fn one(self, db: &DatabaseConnection) -> DbResult<Option<M::Model>> {
        self.build_query().one(db).await
    }

    /// 获取匹配记录的总数
    pub async fn count(self, db: &DatabaseConnection) -> DbResult<u64> {
        let mut count_query = self.build_query();
        count_query = count_query
            .select_only()
            .column_as(Expr::cust("COUNT(*)"), "count");

        let count_result = count_query
            .into_model::<CountResult>()
            .one(db)
            .await?;

        Ok(count_result.map(|r| r.count as u64).unwrap_or(0))
    }

    /// 根据构建的条件执行更新
    /// 根据构建的条件执行更新
    pub async fn update(
        self,
        db: &DatabaseConnection,
        model: M::ActiveModel
    ) -> DbResult<u64> {
        let condition = self.build_condition();
        let mut update = M::update_many();

        // 遍历所有列
        for col in M::Column::iter() {
            // 直接获取 ActiveValue<Value>
            let active_value = model.get(col);

            // 处理 Set 值且非空的情况
            if let sea_orm::ActiveValue::Set(val) = active_value {
                if !is_empty_value(&val) {
                    let expr = sea_orm::sea_query::SimpleExpr::from(
                        Expr::val(val.clone())
                    );
                    update = update.col_expr(col, expr);
                }
            }
        }

        update
            .filter(condition)
            .exec(db)
            .await
            .map(|res| res.rows_affected)
    }

    /// 根据构建的条件执行删除
    pub async fn delete(self, db: &DatabaseConnection) -> DbResult<u64> {
        let condition = self.build_condition();
        M::delete_many()
            .filter(condition)
            .exec(db)
            .await
            .map(|res| res.rows_affected) // 字段访问，不是方法
    }

    /// 构建条件表达式（私有方法）
    fn build_condition(&self) -> Condition {
        let mut condition = Condition::all();

        for expr in &self.conditions {
            let sub_condition = self.build_condition_expr(expr);
            condition = condition.add(sub_condition);
        }

        condition
    }
}

/// 分页结果结构
#[derive(Serialize, Deserialize,Default, Debug, Clone)]
pub struct PaginatedResult<T> {
    pub items: Vec<T>,
    pub total: u64,
    pub page: u64,
    pub page_size: u64,
    pub total_pages: u64,
}

/// 自定义SQL分页结果
#[derive(Serialize, Deserialize, Debug, Clone)]
pub struct SqlPaginatedResult {
    pub items: Vec<serde_json::Value>,
    pub total: u64,
    pub page: u64,
    pub page_size: u64,
    pub total_pages: u64,
}

/// 计数查询结果
#[derive(Debug, Clone, FromQueryResult)]
struct CountResult {
    count: i64,
}

/// 数据库操作工具类 (SQLite 优化版)
#[derive(Clone, Debug)]
pub struct DBUtil {
    connection: DatabaseConnection,
}

impl DBUtil {
    /// 创建新的DBUtil实例
    pub fn new(connection: DatabaseConnection) -> Self {
        Self { connection }
    }

    /// 获取数据库连接
    pub fn get_connection(&self) -> &DatabaseConnection {
        &self.connection
    }

    /// 开始事务
    pub async fn begin_transaction(&self) -> DbResult<DatabaseTransaction> {
        self.get_connection().begin().await
    }

    /// 提交事务
    pub async fn commit_transaction(&self, transaction: DatabaseTransaction) -> DbResult<()> {
        transaction.commit().await
    }

    /// 回滚事务
    pub async fn rollback_transaction(&self, transaction: DatabaseTransaction) -> DbResult<()> {
        transaction.rollback().await
    }

    /// 插入数据
    pub async fn insert<M>(&self, model: M) -> DbResult<<M::Entity as EntityTrait>::Model>
        where
            M: ActiveModelTrait + Send + 'static + sea_orm::ActiveModelBehavior,
            M::Entity: EntityTrait,
            <<M as ActiveModelTrait>::Entity as EntityTrait>::Model: IntoActiveModel<M>,
    {
        let conn = self.get_connection();
        model.insert(conn).await
    }

    /// 更新数据
    pub async fn update<M>(&self, model: M) -> DbResult<<M::Entity as EntityTrait>::Model>
        where
            M: ActiveModelTrait + Send + 'static + sea_orm::ActiveModelBehavior,
            M::Entity: EntityTrait,
            <<M as ActiveModelTrait>::Entity as EntityTrait>::Model: IntoActiveModel<M>,
    {
        let conn = self.get_connection();
        model.update(conn).await
    }

    /// 删除数据
    pub async fn delete<M>(
        &self,
        id: <M::PrimaryKey as sea_orm::PrimaryKeyTrait>::ValueType,
    ) -> DbResult<()>
        where
            M: EntityTrait + Send + Sync + 'static,
            M::PrimaryKey: sea_orm::PrimaryKeyTrait,
    {
        let conn = self.get_connection();
        M::delete_by_id(id).exec(conn).await?;
        Ok(())
    }

    /// 查询构建器
    pub fn query<M>(&self) -> QueryBuilder<M>
        where
            M: EntityTrait + Default + Send + Sync + 'static,
            M::Column: ColumnTrait + Send + Sync + 'static,
    {
        QueryBuilder::new(M::default())
    }

    /// 执行自定义SQL查询
    pub async fn raw_query(
        &self,
        sql: &str,
        params: Vec<Value>,
    ) -> DbResult<Vec<serde_json::Value>> {
        let conn = self.get_connection();
        let stmt = Statement::from_sql_and_values(DbBackend::Sqlite, sql, params);
        let rows = conn.query_all(stmt).await?;

        let mut results = Vec::new();
        for row in rows {
            let json_row = self.row_to_json(&row)?;
            results.push(json_row);
        }

        Ok(results)
    }

    /// 将数据库行转换为JSON对象 (SQLite 优化)
    fn row_to_json(&self, row: &QueryResult) -> DbResult<serde_json::Value> {
        let mut map = serde_json::Map::new();

        for column_name in row.column_names() {
            // SQLite 返回的列名可能包含表名前缀，我们只取列名部分
            let col_name = column_name.split('.').last().unwrap_or(&column_name);

            // 处理 SQLite 的特殊类型
            let value = if let Ok(v) = row.try_get::<String>("", col_name) {
                serde_json::Value::String(v)
            } else if let Ok(v) = row.try_get::<i64>("", col_name) {
                serde_json::Value::Number(v.into())
            } else if let Ok(v) = row.try_get::<f64>("", col_name) {
                serde_json::Value::Number(
                    serde_json::Number::from_f64(v).unwrap_or(serde_json::Number::from(0)),
                )
            } else if let Ok(v) = row.try_get::<bool>("", col_name) {
                serde_json::Value::Bool(v)
            } else if let Ok(v) = row.try_get::<Vec<u8>>("", col_name) {
                // 使用新的 base64 编码方式
                serde_json::Value::String(general_purpose::STANDARD.encode(&v))
            } else if let Ok(v) = row.try_get::<chrono::NaiveDateTime>("", col_name) {
                serde_json::Value::String(v.to_string())
            } else {
                // 回退到 NULL
                serde_json::Value::Null
            };

            map.insert(col_name.to_string(), value);
        }

        Ok(serde_json::Value::Object(map))
    }

    /// 执行自定义SQL语句（用于更新、删除等）
    pub async fn execute(&self, sql: &str, params: Vec<Value>) -> DbResult<u64> {
        let conn = self.get_connection();
        let stmt = Statement::from_sql_and_values(DbBackend::Sqlite, sql, params);
        let result = conn.execute(stmt).await?;
        Ok(result.rows_affected())
    }
    /// 执行无参数的自定义SQL语句（用于更新、删除等）
    pub async fn execute_without_params(&self, sql: &str) -> DbResult<u64> {
        let conn = self.get_connection();
        let stmt = Statement::from_string(DbBackend::Sqlite, sql.to_owned());
        let result = conn.execute(stmt).await?;
        Ok(result.rows_affected())
    }


    pub async fn execute_with<I>(
        &self,
        sql: &str,
        params: I
    ) -> DbResult<u64>
        where
            I: IntoIterator,
            I::Item: Into<Value>,  // 自动转换元素为 Value
    {
        // 自动转换参数为 Vec<Value>
        let values: Vec<Value> = params.into_iter().map(|item| item.into()).collect();

        // 复用原有逻辑
        self.execute(sql, values).await
    }

    /// 自定义SQL分页查询 (SQLite 优化)
    pub async fn paginate_with_sql(
        &self,
        data_sql: &str,
        count_sql: &str,
        mut params: Vec<Value>,
        page: u64,
        page_size: u64,
    ) -> DbResult<SqlPaginatedResult> {
        let conn = self.get_connection();
        let offset = (page - 1) * page_size;

        // 1. 执行计数查询
        let count_stmt = Statement::from_sql_and_values(DbBackend::Sqlite, count_sql, params.clone());
        let count_result = conn.query_one(count_stmt).await?;

        let total = count_result
            .and_then(|row| row.try_get::<i64>("", "count").ok())
            .unwrap_or(0) as u64;

        // 2. 准备数据查询参数 (SQLite 分页参数顺序: LIMIT, OFFSET)
        // 注意：SQLite 使用 ?1, ?2 占位符，这里直接追加到参数列表
        params.push(Value::Int(Some(page_size as i32)));
        params.push(Value::Int(Some(offset as i32)));

        // 3. 执行数据查询
        let data_stmt = Statement::from_sql_and_values(DbBackend::Sqlite, data_sql, params);
        let rows = conn.query_all(data_stmt).await?;

        // 4. 转换为JSON
        let items = rows
            .into_iter()
            .map(|row| self.row_to_json(&row))
            .collect::<DbResult<Vec<_>>>()?;

        // 5. 计算总页数
        let total_pages = if page_size > 0 {
            (total as f64 / page_size as f64).ceil() as u64
        } else {
            0
        };

        Ok(SqlPaginatedResult {
            items,
            total,
            page,
            page_size,
            total_pages,
        })
    }

    /// 简化版SQL分页 (SQLite 优化)
    pub async fn simple_paginate_with_sql(
        &self,
        base_sql: &str,
        params: Vec<Value>,
        page: u64,
        page_size: u64,
    ) -> DbResult<SqlPaginatedResult> {
        // 自动生成计数SQL
        let count_sql = format!("SELECT COUNT(*) AS count FROM ({}) AS subquery", base_sql);

        // 添加 SQLite 分页语法
        let data_sql = format!("{} LIMIT ? OFFSET ?", base_sql);

        self.paginate_with_sql(&data_sql, &count_sql, params, page, page_size)
            .await
    }

    /// 类型安全的SQL查询
    pub async fn query_as<T: FromQueryResult>(
        &self,
        sql: &str,
        params: Vec<Value>,
    ) -> DbResult<Vec<T>> {
        let conn = self.get_connection();
        let stmt = Statement::from_sql_and_values(DbBackend::Sqlite, sql, params);
        T::find_by_statement(stmt).all(conn).await
    }

}

pub fn opt_str_to_value(opt: Option<String>) -> Value {
    match opt {
        Some(s) => Value::String(Some(Box::new(s))),
        None => Value::String(None),
    }
}

/// 辅助方法：判断Value是否为空（空字符串或None）
pub fn is_empty_value(value: &Value) -> bool {
    match value {
        Value::String(Some(s)) => s.trim().is_empty(), // 空字符串（含纯空格）
        Value::String(None) => true,                   // 字符串None
        Value::BigDecimal(None) => true,               // 数值None
        Value::Bytes(None) => true,                    // 字节None
        // 其他变体（如DateTime、Boolean等）在新版本中可能被合并为BigDecimal或String
        _ => false,                                    // 非空值
    }
}